using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using NUnit.Framework;

namespace SDG.UnitTesting.SimpleTalk.Tests
{
    public static class SqlScript
    {
        public static string GetConnectionString(string name)
        {
            return ConfigurationManager.ConnectionStrings[name].ConnectionString;
        }

        public static void ExecuteNonQuery(string connectionName, string sql)
        {
            SqlConnection sqlConn = new SqlConnection(GetConnectionString(connectionName));
            SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
            try
            {
                sqlConn.Open();
                sqlCmd.ExecuteNonQuery();
            }
            finally
            {
                if (sqlConn.State != ConnectionState.Open)
                    sqlConn.Close();
            }
        }
    }

    [TestFixture]
    public class SqlScriptTests
    {
        [Test]
        public void GetConnectionString_ValidName_ConnectionToNorthwind()
        {
            string conn = SqlScript.GetConnectionString("NorthwindConnection");
            Assert.IsTrue(conn.Contains("Server=(local)"), "Does not contain server");
            Assert.IsTrue(conn.Contains("Database=Northwind"), "Does not contain database");
        }

        [Test]
        public void ExecuteTests()
        {
            SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnection"].ConnectionString);
            SqlCommand sqlCmd = new SqlCommand("CREATE TABLE TestTable (ID INT)", sqlConn);
            sqlConn.Open();
            sqlCmd.ExecuteNonQuery();
            sqlConn.Close();


            SqlScript.ExecuteNonQuery("NorthwindConnection", "DROP TABLE TestTable");


            SqlConnection sqlConn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnection"].ConnectionString);
            SqlCommand sqlCmd2 = new SqlCommand("SELECT OBJECT_ID('Test Table')", sqlConn2);
            sqlConn2.Open();
            object t = sqlCmd2.ExecuteScalar();
            sqlConn2.Close();
            Assert.AreEqual(DBNull.Value, t);
        }
    }
    
}
